﻿<%@ WebHandler Language="C#" Class="ZSMissingPartsDashBoard" %>

using System;
using System.IO;
using System.Web;
using Newtonsoft.Json;
using Newtonsoft.Json.Linq;
using Newtonsoft.Json.Converters;
using System.Collections.Generic;
using System.Data;
using System.Text;
using System.Net;
using System.Collections;
using ReportBusiness;
/**
 * Description: 发动机总体事业部缺件统计看板
 * Copyright (c): 通力凯顿（北京）系统集成有限公司
 * Author: zhunan
 * Date: 2022.6.20
 */

public class ZSMissingPartsDashBoard : IHttpHandler
{
    private uMESReportOracleDAL dal = new uMESReportOracleDAL();
    public bool IsReusable { get { return false; } }
    public void ProcessRequest(HttpContext context)
    {
        string requestjson = "";
        using (StreamReader reader = new StreamReader(context.Request.InputStream)) { requestjson = reader.ReadToEnd(); }
        JObject jObject = JsonConvert.DeserializeObject(requestjson) as JObject;

        //查询
        if (jObject["type"] != null && jObject["type"].ToString() == "getData")
        {
            Result r = getData(jObject);
            context.Response.ContentType = "text/json";
            context.Response.Write(JsonConvert.SerializeObject(r));
            context.Response.End();
        }

        //导出
        if (jObject["type"] != null && jObject["type"].ToString() == "getExportExcel")
        {
            getExportExcel(jObject);
        }
    }

    //查询公用sql
    string getQuerySql(JObject jObject)
    {
        string sql = @"
SELECT wf.description,
       u.attributevalue,
       mfg.mfgordername,
       pb.productname,
       e.containerno,
       e.containername,
       wfs.workflowstepname,
       decode(wf.workflowtype,'施工工艺','临时','技通工艺','临时','正常') workflowtype,
       s.specno,
       s.opdesc,
       e.plannedstartdate,
       e.plannedfinishdate,
       cl.jh 小零件件号,
       cl.jhmc 小零件名称,
       cl.firstassembleqty,
       cl.confirmqty,
       cl.firstassembleqty - cl.confirmqty noconfirmqty
FROM enginepartplan e
INNER JOIN Workflowstep wfs ON wfs.workflowstepid = e.workflowstepid
INNER JOIN (
      SELECT ep.containerno,MIN(wfs.sequence) wfssequence
      FROM enginepartplan ep
      LEFT JOIN Workflowstep wfs ON wfs.workflowstepid = ep.workflowstepid
      WHERE ep.usecompleted = '0' AND ep.containerno IS NOT NULL
      GROUP BY ep.containerno
) ep ON ep.containerno = e.containerno AND ep.wfssequence = wfs.sequence
INNER JOIN mfgorder mfg ON mfg.mfgorderid = e.mfgorderid AND mfg.productid != e.productid
LEFT JOIN Userattribute u ON u.parentid = mfg.mfgorderid AND u.userattributename = '发动机'
LEFT JOIN product p ON e.productid = p.productid
LEFT JOIN productbase pb ON pb.productbaseid = p.productbaseid
LEFT JOIN Workflow wf ON wf.workflowid = e.workflowid
LEFT JOIN specbase sb_ ON sb_.specbaseid = wfs.specbaseid
LEFT JOIN spec s ON s.specid = NVL(sb_.revofrcdid,wfs.specid)
LEFT JOIN (
   SELECT COUNT(b.bomid) countnum,
          listagg(bt.billtypename,'、') WITHIN GROUP (ORDER BY bt.billtypename) billtypename,
          listagg(DECODE(bt.billtypename,'大小件目录',b.bomid,NULL),'、') WITHIN GROUP (ORDER BY DECODE(bt.billtypename,'大小件目录',b.bomid,NULL)) bomid,
          b.workflowid
   FROM bom b,billtype bt
   WHERE b.billtypeid = bt.billtypeid
   GROUP BY b.workflowid
) b ON b.workflowid = e.workflowid
LEFT JOIN (
     SELECT w.workflowid,pm.firstassembleqty,im.containerno,
            cm.productname jh,p.description jhmc,SUM(NVL(con.confirmqty,0)) confirmqty
     FROM issueerpmaterial im
     LEFT JOIN collecterpmaterial cm ON im.collecterpmaterialid = cm.collecterpmaterialid
     LEFT JOIN partmenuinfo pm ON im.partmenuinfoid = pm.partmenuinfoid
     LEFT JOIN product p ON p.productid =pm.productid
     LEFT JOIN Workflowstep w ON w.workflowstepid = im.workflowstepid
     LEFT JOIN (
          SELECT SUM(mi.confirmqty) confirmqty,issueerpmaterialid
          FROM issueerpmaterialconfirmation mi
          GROUP BY mi.issueerpmaterialid
     ) con ON con.issueerpmaterialid = im.issueerpmaterialid
     WHERE w.workflowid IS NOT NULL AND im.containerno IS NOT NULL
     GROUP BY w.workflowid,pm.firstassembleqty,im.containerno,cm.productname,p.description
) cl ON cl.workflowid = e.workflowid AND cl.containerno = e.containerno
WHERE e.containerno IS NOT NULL AND e.usecompleted = '0' AND b.bomid > '0' AND INSTR(b.billtypename,'大小件目录') >0
      AND e.containerno IS NOT NULL
";
        return sql;
    }

    Result getData(JObject jObject)
    {
        string sql = getQuerySql(jObject);
        uMESPagingDataDTO dto = dal.GetPagingDataIns(sql, (int)jObject["currentPage"], (int)jObject["pageSize"]);
        DataTable dtResult = dto.DBTable;

        foreach (DataColumn col in dtResult.Columns)
        {
            col.ColumnName = col.ColumnName.ToLower();
        }

        Result r = new Result();
        r.data = dtResult;
        r.total = Convert.ToInt32(dto.RowCount);
        r.meta = new Result.Meta(200, "请求成功");
        return r;
    }

    //导出
    void getExportExcel(JObject jObject)
    {
        string sql = getQuerySql(jObject);
        DataTable dtResult = dal.GetDataTable(sql);

        foreach (DataColumn col in dtResult.Columns)
        {
            col.ColumnName = col.ColumnName.ToLower();
        }

        dtResult.Columns["productname"].Caption = "零件图号";
        dtResult.Columns["productdesc"].Caption = "零件名称";
        dtResult.Columns["containername"].Caption = "批次号";
        dtResult.Columns["workflowstepname"].Caption = "工序";
        dtResult.Columns["reportqty"].Caption = "数量";
        dtResult.Columns["workcentername"].Caption = "工区";
        dtResult.Columns["delayhours"].Caption = "等待时间";
        dtResult.Columns["reportdate"].Caption = "工人报工时间";
        dtResult.Columns["status"].Caption = "状态";
        dtResult.Columns["fullname"].Caption = "检验员";
        dtResult.Columns["otherdalayhours"].Caption = "其他等待时间";
        dtResult.Columns["notes"].Caption = "备注";
        ExportHelper.ExportExcel(dtResult, "缺件统计看板");
    }
}